﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;

namespace TravelexForwardContracts
{
    class XPaymentExcel
    {
        /* ******************************************************
         *  Data Members
         * ******************************************************/
        private DateTime m_specifiedDateTime;
        private String m_filepath;

        /* *******************************************************
         *  Public Interface
         * *******************************************************/
        public DataSetPayment processExcel(String in_filepath, DateTime in_updateDate)
        {
            m_filepath = in_filepath;
            m_specifiedDateTime = in_updateDate;

            // Step 1 : Retrieve data from Excel file
            DataSet dsOriPayment = new DataSet();
            dsOriPayment = this.readPaymentExcel(in_filepath);

            // step 2: check validation
            if (!this.checkPaymentExcelFile(dsOriPayment))
            {
                HMsgDialog("[NG]:Check Paid&Unpaid Failed", "Format Invalid");
                return null;
            }

            // Step 3 : delete empty rows
            DataSet dsNoEmptyPayment = new DataSet();
            dsNoEmptyPayment = this.deleteEmptyRows(dsOriPayment);

            // Step 4 : build expected dataset
            DataSetPayment dsValidPayment = new DataSetPayment();
            dsValidPayment = this.buildValidPaymentDataSet(dsNoEmptyPayment, m_specifiedDateTime);

            return dsValidPayment;
        }

        /* ******************************************************
         * Private Functions
         * ******************************************************/
        private DataSet readPaymentExcel(String in_filepath)
        {
            String sqlFindAll = "SELECT * FROM [Sheet1$]";
            String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + in_filepath +
                ";Extended Properties='Excel 8.0;IMEX=1';";
            try
            {
                using (OleDbDataAdapter oleAdapter = new OleDbDataAdapter(sqlFindAll, strConn))
                {
                    DataSet dsOriginal = new DataSet();
                    oleAdapter.Fill(dsOriginal, "[Sheet1$]");
                    return dsOriginal;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("readPaymentExcel Error :" + ex.Message);
            }
        }

        private bool checkPaymentExcelFile(DataSet dsOriginal)
        {

            if (dsOriginal.Tables[0].Columns[0].ToString() != "Logical Number")
            {

                HMsgDialog("[NG]:The 1st column is not 'Logical Number'", "Paid & Unpiad.xls Format invalid");
                return false;
            }
            if (dsOriginal.Tables[0].Columns[1].ToString() != "Name")
            {
                HMsgDialog("[NG]:The 2nd column is not 'Name'", "Paid & Unpiad.xls Format invalid");
                return false;
            }
            if (dsOriginal.Tables[0].Columns[2].ToString() != "Counterparty")
            {
                HMsgDialog("[NG]:The 3rd column is not 'Counterparty'", "Paid & Unpiad.xls Format invalid");
                return false;
            }
            if (dsOriginal.Tables[0].Columns[3].ToString() != "Maturity Date")
            {
                HMsgDialog("[NG]:The 4th column is not 'Maturity Date'", "Paid & Unpiad.xls Format invalid");
                return false;
            }
            if (dsOriginal.Tables[0].Columns[4].ToString() != "Tot Amount")
            {
                HMsgDialog("[NG]:The 5th column is not 'Tot Amount'", "Paid & Unpiad.xls Format invalid");
                return false;
            }
            if (dsOriginal.Tables[0].Columns[5].ToString() != "Paid Status")
            {
                HMsgDialog("[NG]:The 6th column is not 'Paid Status'", "Paid & Unpiad.xls Format invalid");
                return false;
            }
            if (dsOriginal.Tables[0].Columns[6].ToString() != "Localccy")
            {
                HMsgDialog("[NG]:The 8th column is not 'Localccy'", "Paid & Unpiad.xls Format invalid");
                return false;
            }
            if (dsOriginal.Tables[0].Columns[7].ToString() != "Local Amount")
            {
                HMsgDialog("[NG]:The 9th column is not 'Local Amount'", "Paid & Unpiad.xls Format invalid");
                return false;
            }
            if (dsOriginal.Tables[0].Columns[8].ToString() != "Rate")
            {
                HMsgDialog("[NG]:The 9th column is not 'Rate'", "Paid & Unpiad.xls Format invalid");
                return false;
            }

            return true;
        }

        private DataSetPayment buildValidPaymentDataSet(DataSet in_ds, DateTime in_date)
        {
            try
            {
                /* ************** Filter Original DataSet *******************/
                DataSet tmpDataSet = new DataSet();
                tmpDataSet = in_ds.Copy();

                tmpDataSet.Tables[0].Columns.Add("Currency", typeof(string));
                tmpDataSet.Tables[0].Columns.Add("Instrument", typeof(string));
                tmpDataSet.Tables[0].Columns.Add("UpdateDate", typeof(string));

                // Process Currency And Instrument  >>> Start
                int rowNumber = tmpDataSet.Tables[0].Rows.Count;
                String[] tmpCurrencyArray;
                String currencyName = "";
                String[] tmpInstrumentArray;
                String strInstrument = "";
                List<int> delRowIndex = new List<int>();

                for (int i = 0; i < rowNumber; i++)
                {
                    tmpDataSet.Tables[0].Rows[i]["UpdateDate"] = in_date.ToShortDateString();

                    if (tmpDataSet.Tables[0].Rows[i][0].ToString() != "")
                    {
                        if (tmpDataSet.Tables[0].Rows[i][0].GetType() == typeof(string))
                        {
                            string strLogicalNumber = tmpDataSet.Tables[0].Rows[i][0].ToString();
                            Match matchCurrency = Regex.Match((string)strLogicalNumber, @"^Currency", RegexOptions.IgnoreCase);
                            Match matchInstrument = Regex.Match((string)strLogicalNumber, @"^Instrument", RegexOptions.IgnoreCase);
                            if (matchCurrency.Success)
                            {
                                tmpCurrencyArray = ((string)strLogicalNumber).Split(':');
                                currencyName = tmpCurrencyArray[1].Trim();
                                currencyName = currencyName.Replace(" ", "");
                                delRowIndex.Add(i);
                            }
                            else if (matchInstrument.Success)
                            {
                                tmpInstrumentArray = ((string)strLogicalNumber).Split(':');
                                strInstrument = tmpInstrumentArray[1].Trim();
                                strInstrument = strInstrument.Replace(" ", "");
                                delRowIndex.Add(i);
                            }
                        }
                    }

                    if (currencyName != "")
                    {
                        tmpDataSet.Tables[0].Rows[i]["Currency"] = currencyName;
                    }

                    if (strInstrument != "")
                    {
                        tmpDataSet.Tables[0].Rows[i]["Instrument"] = strInstrument;
                    }
                }
                for (int i = 0; i < delRowIndex.Count; i++)
                {
                    int j = delRowIndex[i];
                    tmpDataSet.Tables[0].Rows[j].Delete();
                }
                tmpDataSet.Tables[0].AcceptChanges();  

                // Copy and Return
                DataSetPayment dsPayment = new DataSetPayment();
                foreach (DataRow tmpRow in tmpDataSet.Tables[0].Rows)
                {
                    DataRow row = dsPayment.Tables[0].NewRow();
                    row["LogicalNumber"] = tmpRow["Logical Number"];
                    row["Name"] = tmpRow["Name"];
                    row["Counterparty"] = tmpRow["Counterparty"];
                    row["MaturityDate"] = tmpRow["Maturity Date"];
                    row["TotAmount"] = tmpRow["Tot Amount"];
                    row["PaidStatus"] = tmpRow["Paid Status"];
                    row["Localccy"] = tmpRow["Localccy"];
                    row["Rate"] = tmpRow["Rate"];
                    row["Currency"] = tmpRow["Currency"];
                    row["Instrument"] = tmpRow["Instrument"];
                    row["UpdateDate"] = tmpRow["UpdateDate"];
                    row["LocalAmount"] = tmpRow["Local Amount"];
                    dsPayment.Tables[0].Rows.Add(row);
                }
                dsPayment.AcceptChanges();
                return dsPayment;
            }
            catch (System.Exception ex)
            {
                HMsgDialog("[NG]: buildValidPaymentDataSet", ex.Message);
                return null;
            }
        }

        private DataSet deleteEmptyRows(DataSet in_ds)
        {
            try
            {
                DataSet tmpDataSet = new DataSet();
                tmpDataSet = in_ds.Copy();
                List<int> emptyRowIndex = new List<int>();
                int colIndex = 1;

                foreach (DataRow row in tmpDataSet.Tables[0].Rows)
                {
                    bool isRowsEmpty = false;
                    foreach (DataColumn col in tmpDataSet.Tables[0].Columns)
                    {
                        if (row[col].ToString() != "")
                        {
                            isRowsEmpty = false;
                            break;
                        }
                        else
                            isRowsEmpty = true;
                    }
                    if (isRowsEmpty)
                    {
                        emptyRowIndex.Add(colIndex);
                        row.Delete();
                    }
                    colIndex++;
                }
                tmpDataSet.Tables[0].AcceptChanges();
                return tmpDataSet;
            }
            catch (System.Exception ex)
            {
                throw new Exception("deleteEmptyRows [NG] : " + ex.Message);
            }
        }

        private void HMsgDialog(String msg, String ex)
        {
            using (HMessageBox HMsg = new HMessageBox())
            {
                HMsgDialog(msg, ex);
            }
        }

    }
}
